import datetime
import json
import math
import os
import time
from datetime import datetime

import pymysql
from flask import Flask, jsonify, request, render_template, redirect, abort
from flask_cors import CORS
from ultralytics import YOLO

from resource import category, insect, context
from resource.category import CategoryListReq
from resource.insect import InsectQueryPage

import hashlib

# export DB_HOST=mysql.sqlpub.com
# export DB_PORT=3306
# export DB_USER=leafming
# export DB_PASSWORD=0wzzanhCLUmH51k2
# DB_HOST DB_PORT DB_USER DB_PASSWORD DB_NAME
# conn = pymysql.connect(
#     host=os.getenv('DB_HOST','mysql.sqlpub.com'),
#     port=int(os.getenv('DB_PORT',3306)),
#     user=os.getenv('DB_USER','leafming'),
#     password=os.getenv('DB_PASSWORD','0wzzanhCLUmH51k2'),
#     db=os.getenv('DB_NAME','forestry_protection'),
#     charset='utf8',
#
# )
import pymysqlpool
config={'host':'mysql.sqlpub.com', 'user':'leafming', 'password':'0wzzanhCLUmH51k2', 'database':'forestry_protection', 'autocommit':True}
local_pool = pymysqlpool.ConnectionPool(size=10, maxsize=5, pre_create_num=2, name='pool1', **config)

categoryService = category.CategoryService(local_pool)
insectService = insect.InsectService(local_pool)
model = YOLO(model=os.path.dirname(__file__) + '/best.pt')
app = Flask(__name__)  # 创建Flask对象

CORS(app)


# def connget():
#     con1 = pool1.get_connection()
#     cursor = con1.cursor()
#     _cursor.execute("SELECT ....")
#     myresult = _cursor.fetchall()
#     con1.close()
#
# def conncommit():
#     con1 = pool1.get_connection()
#     cursor = con1.cursor()
#     sql = "update ..."
#     _cursor.execute(sql)
#     con1.commit()
#     con1.close()


def cursor():
    conn = local_pool.get_connection()
    return conn.cursor(),conn

def conn_close(cursor,conn):
    cursor.close()
    conn.close()

@app.route('/login', methods=['GET', 'POST'])
def login():
    _cursor,conn = cursor()
    if request.method == 'POST':
        data = request.get_json()  # 获取前端发送的 JSON 数据
        username = data.get('username')  # 从数据中获取用户名
        password = data.get('password')  # 从数据中获取密码

        password = md5_encryption(password)  # 加密后的密码

        # 执行数据库查询
        select_sql = 'select id,username,password,avatar from user where username = %s'
        _cursor.execute(select_sql, username)
        exist_password = _cursor.fetchone()

        conn_close(_cursor,conn)
        if exist_password is not None and exist_password[2] == password:
            user = context.LoginUser(exist_password[0], exist_password[1], exist_password[3])
            context.loginRefresh(user)
            return jsonify(message='密码正确', code=200, user=user.__dict__)
        else:
            return jsonify(message='密码错误', code=400)


@app.route('/register', methods=['GET', 'POST'])
def register():

    if request.method == 'POST':
        _cursor,conn = cursor()
        data = request.get_json()
        username = data.get('username')
        password = data.get('password')
        print(username)
        select_sql = 'select username from user where username = %s'
        _cursor.execute(select_sql, username)
        exist_username = _cursor.fetchone()

        password = md5_encryption(password)

        print('exist_username', exist_username)
        if exist_username is None and len(password) > 0:
            insert_user_sql = 'insert into user (username,password,avatar) values(%s,%s,%s)'
            _cursor.execute(insert_user_sql, (username, password, '/static/images/tx.jpg'))
            _cursor.connection.commit()
            conn_close(_cursor,conn)
            return jsonify(message='注册成功', code=200)
        else:
            conn_close(_cursor,conn)
            return jsonify(message='用户已存在', code=400)


@app.route('/change-password', methods=['POST'])
def changePwd():
    _cursor,conn = cursor()
    user = checkLogin()
    data = request.get_json()
    oldPwd = data.get('oldPwd')
    oldPwd = md5_encryption(oldPwd)  # 加密旧密码
    password = data.get('newPwd')
    password = md5_encryption(password)  # 加密新密码
    select_sql = 'select username,password from user where id = %s'
    _cursor.execute(select_sql, user.id)
    result = _cursor.fetchone()
    if result is None:
        conn_close(_cursor,conn)
        return jsonify(message='用户不存在', code=400)

    else:
        if result[1] != oldPwd:
            return jsonify(message='旧密码不一致', code=400)
        change_sql = 'update user set password = %s where id = %s '
        _cursor.execute(change_sql, (password, user.id))
        _cursor.connection.commit()
        conn_close(_cursor,conn)
        return jsonify(message='修改成功', code=200)


@app.route('/banners', methods=['GET'])
def indexData():
    needLogin = checkLogin()
    if needLogin is not None and type(needLogin) != context.LoginUser:
        return needLogin
    _cursor,conn = cursor()
    swiper_sql = 'select id,imgurl from swiper'
    _cursor.execute(swiper_sql)
    swiper_list = _cursor.fetchall()
    print(swiper_list)
    column_names01 = [desc[0] for desc in _cursor.description]
    result01 = []
    for data in swiper_list:
        result01.append(dict(zip(column_names01, data)))
    conn_close(_cursor,conn)
    content = {
        'data': result01,
        'code': 200
        # 'result02': result02
    }

    return jsonify(content)


@app.route('/news-list', methods=['GET'])
def news_list():
    needLogin = checkLogin()
    if needLogin is not None and type(needLogin) != context.LoginUser:
        return needLogin

    _cursor,conn = cursor()
    pageNo = request.args.get('page', 1, int)
    pageSize = request.args.get('size', 10, int)
    take = (pageNo - 1) * pageSize


    news_sql = 'select id,title,datetime,imgurl from news order by id desc '
    news_sql += ' limit ' + str(take) + ',' + str(pageSize)
    _cursor.execute(news_sql)
    data = _cursor.fetchall()
    jsonData = context.fetch2Json(data, _cursor.description)
    select_all_sql = 'select count(t.id) from news t '
    _cursor.execute(select_all_sql)
    count = _cursor.fetchone()[0]
    conn_close(_cursor,conn)
    content = {
        'data': jsonData,
        'total': count,
        'page': pageNo,
        'size': pageSize,
        'code': 200
    }

    return jsonify(content)


@app.route('/newsdetail', methods=['GET'])
def newsdetail():
    _cursor,conn = cursor()
    id = request.args.get('id')
    select_detail_sql = 'select id,title,datetime,imgurl,text1,text2,text3,text4 from news where id = %s '
    _cursor.execute(select_detail_sql, id)
    data = _cursor.fetchall()
    jsonData = context.fetch2Json(data, _cursor.description)
    content = {
        'data': jsonData[0],
        'code': 200
    }
    conn_close(_cursor,conn)
    return jsonify(content)


def checkLogin():
    # 小程序请求
    if request.headers.get('x-client-agent', None) is not None:
        token = request.headers.get('x-auth-token', None)
        if (token is None or context.checkWeapp(token) is False):
            abort(401, "auth denied")
        return context.currentUser(token)
    else:
        # admin 请求
        if request.cookies.get('login', -1, int) < 0:
            return render_template('login.html')
        elif request.cookies.get('login', -1, int) + 3600 < int(time.time()):
            return render_template('login.html')
    return None


@app.route('/repairedList', methods=['GET'])
def repairedList():
    needLogin = checkLogin()
    if needLogin is not None:
        return needLogin
    _cursor,conn = cursor()
    state = request.args.get('state')
    username = request.args.get('username')
    print(state, username)
    select_usrid = 'select id from user where username = %s'
    _cursor.execute(select_usrid, username)
    userid = _cursor.fetchone()[0]
    print('userid', userid)

    if state == '今日识别':
        today_dime = datetime.now().strftime('%Y-%m-%d')
        select_sql = ('select record_datetime,record_user_id,record_result from record where record_datetime=%s and '
                      'record_user_id=%s')
        _cursor.execute(select_sql, (today_dime, userid))
        datalist = _cursor.fetchall()
        print(datalist)
        today_list = []
        for data in datalist:
            record_date = data[0].strftime('%Y-%m-%d')  # 将datetime.date对象转换为字符串格式
            record_user_id = data[1]
            record_result = data[2]
            today_list.append([record_date, record_user_id, record_result])
        content = {
            'result01': today_list
        }
        conn_close(_cursor,conn)
        return jsonify(content)
    elif state == '历史识别':
        today_dime = datetime.now().strftime('%Y-%m-%d')
        select_sql = ('select record_datetime,record_user_id,record_result from record where record_datetime!=%s and '
                      'record_user_id=%s')
        _cursor.execute(select_sql, (today_dime, userid))
        datalist = _cursor.fetchall()
        his_list = []
        for data in datalist:
            record_date = data[0].strftime('%Y-%m-%d')  # 将datetime.date对象转换为字符串格式
            record_user_id = data[1]
            record_result = data[2]
            his_list.append([record_date, record_user_id, record_result])
        content = {
            'result02': his_list
        }
        conn_close(_cursor,conn)
        return jsonify(content)


@app.route('/index')
def index():
    return render_template('index.html')


@app.route('/')
def adminLogin():
    return render_template('login.html')


@app.route('/adminDoLogin', methods=['GET', 'POST'])
def adminDoLogin():
    _cursor,conn = cursor()

    username = request.form.get('username')
    password = request.form.get('password')
    print(username, password)
    password = md5_encryption(password)

    select_sql = 'select password from user where username = %s'
    _cursor.execute(select_sql, (username,))
    select_password = _cursor.fetchone()[0]

    power_sql = 'select jurisdiction from user where username = %s'
    _cursor.execute(power_sql, (username,))
    select_power = _cursor.fetchone()[0]
    conn_close(_cursor,conn)
    if password == select_password and select_power == 1:
        return 'success'  # 返回一个成功的状态码
    elif select_power == 0:
        return 'not admin'  # 返回一个错误的状态码
    else:
        return 'error'  # 返回一个错误的状态码


@app.route('/userlist', methods=['GET', 'POST'])
def userlist():
    _cursor,conn = cursor()
    if request.method == 'POST':  # 判断是否是搜索请求
        print('搜索')
        keyword = request.form.get('keyword')
        keyword = '%' + keyword + '%'
        select_sql = 'select id, username,password,jurisdiction FROM user where username like %s'
        _cursor.execute(select_sql, (keyword,))
        data_list = _cursor.fetchall()
        count_sql = 'SELECT COUNT(*) FROM user WHERE username LIKE %s'
        _cursor.execute(count_sql, (keyword,))
        count = _cursor.fetchone()[0]
        print(data_list)
        conn_close(_cursor,conn)
        return render_template('userlist.html', data=data_list, count=count)
    else:  # 渲染页面
        page = int(request.args.get('page', 1))
        page_size = 10  # 每页显示的数据条数

        select_data_sql = 'SELECT id,username,password,jurisdiction FROM user ORDER BY id asc'
        _cursor.execute(select_data_sql)

        total_count = _cursor.rowcount
        total_page = math.ceil(total_count / page_size)

        offset = (page - 1) * page_size
        select_page_data_sql = f'{select_data_sql} LIMIT {offset}, {page_size}'
        _cursor.execute(select_page_data_sql)
        data = _cursor.fetchall()

        select_all_sql = 'select count(id) from user'
        _cursor.execute(select_all_sql)
        count = _cursor.fetchone()[0]
        conn_close(_cursor,conn)
        return render_template('userlist.html', data=data, page=page, total_page=total_page, count=count)


@app.route('/user_del', methods=['GET', 'POST'])
def user_del():
    _cursor,conn = cursor()
    id = request.form.get('id')
    delete_sql = 'delete from user where id = %s'
    _cursor.execute(delete_sql, (id,))
    print(delete_sql, (id,))
    _cursor.connection.commit()
    print('删除成功')
    conn_close(_cursor,conn)
    return jsonify(status=1, info='删除成功')


@app.route('/user_update', methods=['GET', 'POST'])
def user_update():
    _cursor,conn = cursor()
    id = request.args.get('id')
    print(id)
    select_sql = 'select id,username,password,jurisdiction from user where id = %s'
    _cursor.execute(select_sql, (id,))
    datalist = _cursor.fetchall()
    print(datalist)
    _cursor.nextset()  # 添加这一行代
    id = datalist[0][0]
    username = datalist[0][1]
    password = datalist[0][2]

    password = md5_encryption(password)

    jurisdiction = datalist[0][3]
    conn_close(_cursor,conn)
    return render_template('user_update.html'
                           , id=id, username=username, password=password,
                           jurisdiction=jurisdiction)


@app.route('/user_do_update', methods=['GET', 'POST'])
def user_do_update():
    _cursor,conn = cursor()
    id = request.form.get('id')
    username = request.form.get('username')
    password = request.form.get('password')

    password = md5_encryption(password)

    jurisdiction = request.form.get('jurisdiction')

    update_sql = 'update user set username=%s,password=%s,jurisdiction=%s where id = %s'
    _cursor.execute(update_sql, (username, password, jurisdiction, id))
    _cursor.connection.commit()
    conn_close(_cursor,conn)
    return '修改成功'


@app.route('/user_add', methods=['GET', 'POST'])
def user_add():
    return render_template('user_add.html')


@app.route('/user_do_add', methods=['GET', 'POST'])
def user_do_add():
    _cursor,conn = cursor()
    username = request.form.get('username')
    password = request.form.get('password')

    password = md5_encryption(password)

    insert_sql = 'insert into user (username,password) values(%s,%s) '
    _cursor.execute(insert_sql, (username, password))
    _cursor.connection.commit()
    conn_close(_cursor,conn)
    return '添加成功'


@app.route('/newslist', methods=['GET', 'POST'])
def newslist():
    _cursor,conn = cursor()
    if request.method == 'POST':  # 判断是否是搜索请求
        print('搜索')
        keyword = request.form.get('keyword')
        keyword = '%' + keyword + '%'
        select_sql = 'select id, title,datetime,imgurl,text1,text2,text3,text4 FROM news where title like %s'
        _cursor.execute(select_sql, (keyword,))
        data_list = _cursor.fetchall()
        count_sql = 'SELECT COUNT(*) FROM news WHERE title LIKE %s'
        _cursor.execute(count_sql, (keyword,))
        count = _cursor.fetchone()[0]
        print(data_list)
        conn_close(_cursor,conn)
        return render_template('newslist.html', data=data_list, count=count)
    else:  # 渲染页面
        page = int(request.args.get('page', 1))
        page_size = 10  # 每页显示的数据条数

        select_data_sql = 'SELECT id, title,datetime,imgurl,text1,text2,text3,text4 FROM news ORDER BY id asc'
        _cursor.execute(select_data_sql)

        total_count = _cursor.rowcount
        total_page = math.ceil(total_count / page_size)

        offset = (page - 1) * page_size
        select_page_data_sql = f'{select_data_sql} LIMIT {offset}, {page_size}'
        _cursor.execute(select_page_data_sql)
        data = _cursor.fetchall()

        select_all_sql = 'select count(id) from news'
        _cursor.execute(select_all_sql)
        count = _cursor.fetchone()[0]
        conn_close(_cursor,conn)
        return render_template('newslist.html', data=data, page=page, total_page=total_page, count=count)


@app.route('/news_add', methods=['GET', 'POST'])
def news_add():
    return render_template('news-add.html')


@app.route('/news_do_add', methods=['GET', 'POST'])
def news_do_add():
    _cursor,conn = cursor()
    title = request.form.get('title')
    nowtime = datetime.datetime.now().strftime('%F %T')
    img_url = request.form.get('img_url')
    text1 = request.form.get('text1')
    text2 = request.form.get('text2')
    text3 = request.form.get('text3')
    text4 = request.form.get('text4')
    insert_sql = 'insert into news (title,datetime,imgurl,text1,text2,text3,text4) values(%s,%s,%s,%s,%s,%s,%s) '
    _cursor.execute(insert_sql, (title, nowtime, img_url, text1, text2, text3, text4))
    _cursor.connection.commit()
    conn_close(_cursor,conn)
    return '添加成功'


@app.route('/news_update', methods=['GET', 'POST'])
def news_update():
    _cursor,conn = cursor()
    id = request.args.get('id')

    select_sql = 'select id,title,datetime,imgurl,text1,text2,text3,text4 from news where id = %s'
    _cursor.execute(select_sql, (id,))
    datalist = _cursor.fetchall()
    _cursor.nextset()  # 添加这一行代
    id = datalist[0][0]
    title = datalist[0][1]
    datetime = datalist[0][2]
    imgurl = datalist[0][3]
    text1 = datalist[0][4]
    text2 = datalist[0][5]
    text3 = datalist[0][6]
    text4 = datalist[0][7]
    # print(id,declarer)
    conn_close(_cursor,conn)
    return render_template('news-update.html', id=id, title=title, datetime=datetime, imgurl=imgurl,
                           text1=text1, text2=text2, text3=text3, text4=text4)


@app.route('/news_do_update', methods=['GET', 'POST'])
def news_do_update():
    print('提交了')
    _cursor,conn = cursor()
    id = request.form.get('id')
    title = request.form.get('title')
    datetime = request.form.get('datetime')
    imgurl = request.form.get('imgurl')
    text1 = request.form.get('text1')
    text2 = request.form.get('text2')
    text3 = request.form.get('text3')
    text4 = request.form.get('text4')
    update_sql = 'update news set title=%s,datetime=%s,imgurl=%s,text1=%s,text2=%s,text3=%s,text4=%s where id = %s'
    _cursor.execute(update_sql, (title, datetime, imgurl, text1, text2, text3, text4, id))
    _cursor.connection.commit()
    conn_close(_cursor,conn)
    # response = {'message': '修改成功'}
    return '修改成功'


@app.route('/news_del', methods=['GET', 'POST'])
def news_del():
    _cursor,conn = cursor()
    id = request.form.get('id')
    delete_sql = 'delete from news where id = %s'
    _cursor.execute(delete_sql, (id,))
    print(delete_sql, (id,))
    _cursor.connection.commit()
    print('删除成功')
    conn_close(_cursor,conn)
    return jsonify(status=1, info='删除成功')


@app.route('/swiper_add', methods=['GET', 'POST'])
def swiper_add():
    return render_template('swiper-add.html')


@app.route('/swiper_do_add', methods=['GET', 'POST'])
def swiper_do_add():
    _cursor,conn = cursor()
    imgurl = request.form.get('imgurl')
    insert_sql = 'insert into swiper (imgurl) values(%s) '
    _cursor.execute(insert_sql, (imgurl))

    _cursor.connection.commit()
    conn_close(_cursor,conn)
    return '添加成功'


@app.route('/swiper_del', methods=['GET', 'POST'])
def swiper_del():
    _cursor,conn = cursor()
    id = request.form.get('id')
    delete_sql = 'delete from swiper where id = %s'
    _cursor.execute(delete_sql, (id,))
    print(delete_sql, (id,))
    _cursor.connection.commit()
    conn_close(_cursor,conn)
    print('删除成功')
    return jsonify(status=1, info='删除成功')


# ------------------------------
@app.route('/category_list', methods=['GET', 'POST'])
def category_list():
    level = request.args.get('level', 1, int)
    status = request.args.get('status', -1, int)
    page = int(request.args.get('page', 1, int))
    page_size = 10

    req = CategoryListReq(page, page_size,
                          request.args.get('category_no', None),
                          request.args.get('category_name', None),
                          request.args.get('category_alias', None),
                          int(status),
                          int(level),
                          )
    query = {
        'category_no': request.args.get('category_no', '', str),
        'category_name': request.args.get('category_name', '', str),
        'category_alias': request.args.get('category_alias', '', str),
        'status': request.args.get('status', -1, int),
    }
    result = categoryService.categoryList(req)
    return render_template('category-list.html', level=level, data=result.data, page=page, total_page=result.total_page,
                           query=query,
                           count=result.total)


@app.route('/category_add', methods=['GET', 'POST'])
def category_add():
    if request.method == 'POST':  # 判断是否是搜索请求
        categoryService.add(request.form)
        return 'success'
    else:
        level = request.args.get('level', 1, int)
        if (level > 1):
            level2List = categoryService.categorySimpleList(1, -1)
            return render_template('category-add.html', level=level, level2List=level2List)
        else:
            return render_template('category-add.html', level=level)


@app.route('/category_edit', methods=['GET', 'POST'])
def category_edit():
    id = request.args.get("id", -1, int)
    if request.method == 'POST':  # 判断是否是搜索请求
        categoryService.edit(request.form)
        return 'success'
    else:
        level = request.args.get('level', 1, int)
        data = categoryService.detail(id)
        if (level > 1):
            level2List = categoryService.categorySimpleList(1, -1, cateNo=data['category_no'])
            return render_template('category-edit.html', data=data, level2List=level2List)
        else:
            return render_template('category-edit.html', data=data)


@app.route('/category_delete', methods=['POST'])
def category_delete():
    id = request.form.get('id')
    categoryService.drop(id)
    return jsonify(status=1, info='删除成功')


@app.route('/category-simple-list', methods=['GET'])
def category_simple_list():
    level = request.args.get('level', 1, int)
    parentId = request.args.get('parentId', -1, int)
    category_no = request.args.get('category_no', None)
    if level > 1:
        result = categoryService.categorySimpleList(level, parentId, category_no)
        return result


# -----
UPLOAD_FOLDER = '/static/images/upload/'
ALLOWED_EXTENSIONS = {'txt', 'pdf', 'png', 'jpg', 'jpeg', 'gif'}


# 允许的上传类型
def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@app.route('/upload', methods=['POST'])
def upload_file():
    if request.method == 'POST':
        # check if the post request has the file part
        if 'file' not in request.files:
            return redirect(request.url)
        file = request.files['file']
        if file.filename == '':
            return redirect(request.url)
        if file and allowed_file(file.filename):
            new_name = context.randomFileName(file.filename)
            file_path = os.path.dirname(os.path.abspath(__file__)) + '/' + UPLOAD_FOLDER + new_name
            file.save(file_path)
            return UPLOAD_FOLDER + new_name


@app.route('/insect_list', methods=['GET', 'POST'])
def insect_list():
    page = int(request.args.get('page', 1, int))
    page_size = 10

    req = InsectQueryPage(page, page_size,
                          request.args.get('category_no', None),
                          request.args.get('insect_name', None),
                          request.args.get('insect_alias', None),
                          request.args.get('status', -1, int),
                          )
    result = insectService.list(req)

    query = {
        'category_no': request.args.get('category_no', '', str),
        'insect_name': request.args.get('insect_name', '', str),
        'insect_alias': request.args.get('insect_alias', '', str),
        'status': request.args.get('status', -1, int),
    }

    return render_template('insect-list.html', data=result.data, page=page, total_page=result.total_page, query=query,
                           count=result.total)


@app.route('/insect_add', methods=['GET', 'POST'])
def insect_add():
    if request.method == 'POST':  # 判断是否是搜索请求
        insectService.add(request.form)
        return 'success'
    else:
        lv1 = categoryService.categorySimpleList(1, -1)
        return render_template('insect-add.html', lv1=lv1)


@app.route('/insect_edit', methods=['GET', 'POST'])
def insect_edit():
    id = request.args.get("id", -1, int)
    if request.method == 'POST':  # 判断是否是搜索请求
        insectService.edit(request.form)
        return 'success'
    else:
        lv1 = categoryService.categorySimpleList(1, -1)
        detail = insectService.detail(id)
        return render_template('insect-edit.html', data=detail, lv1=lv1)


@app.route('/insect_delete', methods=['POST'])
def insect_delete():
    id = request.form.get('id')
    insectService.delete(id)
    return jsonify(status=1, info='删除成功')


@app.route('/map_preview', methods=['GET'])
def insect_detail():
    page = int(request.args.get('page', 1, int))
    page_size = 1000
    req = InsectQueryPage(page, page_size)
    result = insectService.list(req)
    return render_template('map-preview.html', data=json.dumps(result.data, ensure_ascii=False))


@app.route('/insect_match', methods=['POST'])
def match():
    needLogin = checkLogin()
    if needLogin is not None and type(needLogin) != context.LoginUser:
        return needLogin
    req_data = request.get_json()  # 获取前端发送的 JSON 数据
    path = req_data.get('path')  # 从数据中获取用户名
    final_path = os.path.dirname(__file__) + path
    results = model(final_path, project='./yolo_v8')
    if (len(results) > 0):
        result = results[0]
        names = result.names
        props = result.probs
        if (props.top1 >= 0 and props.top1conf.H > 0.7):
            idx = names[props.top1]
            id = insectService.match(path, needLogin.id, idx)
            return jsonify(code=200, data=insectService.detail(id), )

    insectService.match(path, needLogin.id, '-1')
    return jsonify(code=200, data={})


@app.route('/match_record', methods=['GET'])
def match_records():
    needLogin = checkLogin()
    if needLogin is not None and type(needLogin) != context.LoginUser:
        return needLogin

    _cursor,conn = cursor()

    state = request.args.get('state')
    page = int(request.args.get('page', 1, int))
    page_size = 10
    take = (page - 1) * page_size
    today_dime = datetime.now().strftime('%Y-%m-%d')

    result = []
    where = 'where 1=1 '
    if state == '1':
        where += ' and date_format(record_datetime,"%%Y-%%m-%%d") = %s and record_user_id=%s '
        sql = 'select id, date_format(record_datetime,"%%Y-%%m-%%d") as record_datetime,record_user_id,record_result,success,insect_id from record '
        sql += where
        sql += 'order by id desc limit ' + str(take) + ',' + str(page_size)

        _cursor.execute(sql, (today_dime, str(needLogin.id)))
        data = _cursor.fetchall()
        result = context.fetch2Json(data, _cursor.description)

    elif state == '2':
        where += ' and date_format(record_datetime,"%%Y-%%m-%%d") !=%s and record_user_id=%s '
        sql = 'select id,  date_format(record_datetime,"%%Y-%%m-%%d") as record_datetime,record_user_id,record_result,success,insect_id from record '
        sql += where
        sql += 'order by id desc limit ' + str(take) + ',' + str(page_size)

        _cursor.execute(sql, (today_dime, str(needLogin.id)))
        data = _cursor.fetchall()
        result = context.fetch2Json(data, _cursor.description)

    count_sql = 'select count(1) from record ' + where
    _cursor.execute(count_sql, (today_dime, str(needLogin.id)))
    count = _cursor.fetchone()[0]

    total_page = int((count / page_size)) + 1
    if count % page_size <= 0:
        total_page -= 1
    conn_close(_cursor,conn)
    return jsonify(code=200, data=result, total=total_page)


@app.route('/match_detail', methods=['GET'])
def match_detail():
    needLogin = checkLogin()
    if needLogin is not None and type(needLogin) != context.LoginUser:
        return needLogin
    _cursor,conn = cursor()
    id = request.args.get('id')

    sql = 'select id, date_format(record_datetime,"%%Y-%%m-%%d") as record_datetime,record_user_id,record_result,success,insect_id,match_file from record '
    sql += 'where id = %s'

    _cursor.execute(sql, (id))
    data = _cursor.fetchall()
    results = context.fetch2Json(data, _cursor.description)
    result = results[0]
    insect_id = result['insect_id']
    if insect_id is not None and insect_id > 0:
        detail = insectService.detail(result['insect_id'])
        return jsonify(code=200, data=result, detail=detail)
    conn_close(_cursor,conn)
    return jsonify(code=200, data=result)


def md5_encryption(data):
    md5 = hashlib.md5()  # 创建一个md5对象
    md5.update(data.encode('utf-8'))  # 使用utf-8编码数据
    return md5.hexdigest()  # 返回加密后的十六进制字符串


if __name__ == '__main__':
    app.run(host='0.0.0.0',port=5000)
